//
//  CLDataBaseController.m
//  安好
//
//  Created by qinlongqing on 14-8-29.
//  Copyright (c) 2014年 冯凯. All rights reserved.
//

#import "CLDataBaseController.h"
#import "NoticeData.h"

static CLDataBaseController *instance = nil;

@interface CLDataBaseController ()
{
@private
    FMDatabase *_dataBase;
}

@end

@implementation CLDataBaseController

+ (CLDataBaseController *)instance{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
        
        [instance createDataBase];
        
    });
    return instance;
}

- (void)createDataBase{
    NSString *dbPath = [NSString stringWithFormat:@"%@/Library/Caches/tijianTest.db",NSHomeDirectory()];
    _dataBase = [[FMDatabase alloc]initWithPath:dbPath];
    if ([_dataBase open]) {
        MyNSLog("创建数据库成功! path=%@",dbPath);
        //创建数据库然后添加表
        if (![_dataBase tableExists:@"ShiLiTest"]) {
            [self createTableWithSQL:@"CREATE TABLE ShiLiTest (Name text,Left text,Right text)"];//添加视力测试表
        }
        
        if (![_dataBase tableExists:@"XinLvTest"]) {
            [self createTableWithSQL:@"CREATE TABLE XinLvTest (Name text,Xinlv text)"];//添加心率测试表
        }
        
        if (![_dataBase tableExists:@"SeJueTest"]) {
            [self createTableWithSQL:@"CREATE TABLE SeJueTest (Name text,SeJueTestType text,SeJueTestResult text)"];//添加色觉测试表
        }
        
        if (![_dataBase tableExists:@"SpecifyDoctors"]) {
            [self createTableWithSQL:@"CREATE TABLE SpecifyDoctors (doctoruid text,doctorname text,avatar text,departmentid text,departmentname text,doclevelid text,userID text)"];//所有的指定医生表
        }
        
        if (![_dataBase tableExists:@"NewestSubscibeNewsMessageForUser"]) {
            [self createTableWithSQL:@"CREATE TABLE NewestSubscibeNewsMessageForUser (content text,dateline text,description text,nid text,pic text,source text,state text,title text,userID text,isReaded text)"];//记录每个用户目前最新的一条订阅消息数据
        }
        if (![_dataBase tableExists:@"SubscibeReadedTable"]) {
            [self createTableWithSQL:@"CREATE TABLE SubscibeReadedTable (nid text,userID text)"];//所有的已读订阅nid
        }
        
        if (![_dataBase tableExists:@"SecretarySystemMessage"]) {
            [self createTableWithSQL:@"CREATE TABLE SecretarySystemMessage (content text,dateline text,pic text,pmsid text,title text,type text,url text,userID text,isReaded text)"];//所有的安好秘书  0:没读  1:已读
        }
        
        if (![_dataBase tableExists:@"SecretaryNoticeMessage"]) {
            [self createTableWithSQL:@"CREATE TABLE SecretaryNoticeMessage (avatar text,dateline text,message text,pcid text,pmid text,recvuid text,recvusername text,subject text,summary text,tid text,type text,uid text,url text,username text,pid text,userID text,isReaded text)"];//所有的notice消息  0:没读  1:已读
        }
        if (![_dataBase tableExists:@"SecretaryNoticeMessagePic"]) {
            [self createTableWithSQL:@"CREATE TABLE SecretaryNoticeMessagePic (pmid text,userID text,picURL text)"];//所有的notice消息所带的pic地址
        }
        
        if (![_dataBase tableExists:@"StepCountUploadForDay"]) {
            [self createTableWithSQL:@"CREATE TABLE StepCountUploadForDay (dayString text,dayAllSteps text,userID text)"];//添加心率测试表
        }

        
        [_dataBase close];
    }
    else{
        MyNSLog("创建数据库失败!");
    }
}

- (void)createTableWithSQL:(NSString *)createTableSQL{
    if ([_dataBase executeUpdate:createTableSQL]) {
        MyNSLog("创建表成功!");
    }
    else{
        MyNSLog("创建表失败");
    }
}

//- (void)deleteTableWithTableName:(NSString *)tableName{
//    [_dataBase open];
//    
//    [_dataBase executeUpdate:[NSString stringWithFormat:@"drop table %@",tableName]];
//    
//    [_dataBase close];
//}

- (void)insertTableWithTestType:(TestType)testType andDict:(NSMutableDictionary *)dict{
    if ([_dataBase open]) {
        NSDate *dates = [NSDate date];
        NSDateFormatter *formatter =  [[NSDateFormatter alloc] init];
        [formatter setDateFormat:@"yyyyMMddHHmm"];
        NSTimeZone* timeZone = [NSTimeZone timeZoneWithName:@"Asia/beijing"];
        [formatter setTimeZone:timeZone];
        NSString *loctime = [formatter stringFromDate:dates];
        
        switch (testType) {
            case shiLiTest:{
                NSString *testUser = [[dict valueForKey:@"testUserName"] stringByAppendingFormat:@"%@",loctime];
                NSString *leftResult = [dict valueForKey:@"leftResult"];
                NSString *rightResult = [dict valueForKey:@"rightResult"];
                [_dataBase executeUpdate:@"INSERT INTO ShiLiTest (Name,Left,Right) VALUES (?,?,?)",testUser,leftResult,rightResult];
            }
                break;
            case xinLvTest:{
                NSString *testUser = [[dict valueForKey:@"testUserName"] stringByAppendingFormat:@"%@",loctime];
                NSString *xinLvResult = [NSString stringWithFormat:@"%ld", [[dict valueForKey:kXinLvTestResult] integerValue]];
                [_dataBase executeUpdate:@"INSERT INTO XinLvTest (Name,Xinlv) VALUES (?,?)",testUser,xinLvResult];
            }
                break;
            case seJueTest:{
                NSString *testUser = [[dict valueForKey:@"testUserName"] stringByAppendingFormat:@"%@",loctime];
                NSString *seJueTestType = [dict valueForKey:kSeJueTestType];
                NSString *seJueTestResult = [dict valueForKey:kSeJueTestResult];
                [_dataBase executeUpdate:@"INSERT INTO SeJueTest (Name,SeJueTestType,SeJueTestResult) VALUES (?,?,?)",testUser,seJueTestType,seJueTestResult];
            }
                break;


            default:
                break;
        }
        
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (NSArray *)selectAllWithTestType:(TestType)testType{
    if ([_dataBase open]) {
        NSMutableArray *allTestResultArray = [[NSMutableArray alloc]init];
        switch (testType) {
            case shiLiTest:{
                FMResultSet *resultSet=[_dataBase executeQuery:@"SELECT * FROM ShiLiTest"];
                while ([resultSet next]) {
                    NSString *testUserWithDate = [resultSet stringForColumn:@"Name"];
//                    testUserWithDate = [testUserWithDate substringToIndex:testUserWithDate.length - 12];
                    
                    NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:testUserWithDate,@"testUserName",[resultSet stringForColumn:@"Left"],@"leftResult",[resultSet stringForColumn:@"Right"],@"rightResult", nil];
                    [allTestResultArray addObject:dict];
                }
            }
                break;
            case xinLvTest:{
                FMResultSet *resultSet=[_dataBase executeQuery:@"SELECT * FROM XinLvTest"];
                while ([resultSet next]) {
                    NSString *testUserWithDate = [resultSet stringForColumn:@"Name"];
//                    testUserWithDate = [testUserWithDate substringToIndex:testUserWithDate.length - 11];
                    
                    NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:testUserWithDate,@"testUserName",[resultSet stringForColumn:@"Xinlv"],kXinLvTestResult, nil];
                    [allTestResultArray addObject:dict];
                }
            }
                break;
            case seJueTest:{
                FMResultSet *resultSet=[_dataBase executeQuery:@"SELECT * FROM SeJueTest"];
                while ([resultSet next]) {
                    NSString *testUserWithDate = [resultSet stringForColumn:@"Name"];
//                    testUserWithDate = [testUserWithDate substringToIndex:testUserWithDate.length - 11];
                    
                    NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:testUserWithDate,@"testUserName",[resultSet stringForColumn:@"SeJueTestType"],kSeJueTestType,[resultSet stringForColumn:@"SeJueTestResult"],kSeJueTestResult, nil];
                    
                    [allTestResultArray addObject:dict];
                }
            }
                break;

            default:
                break;
        }
        
        [_dataBase close];
        return allTestResultArray;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return nil;
}

- (void)insertSpecifyDoctorWithDict:(NSDictionary *)dict{
    if ([_dataBase open]) {

        NSString *doctoruid = [dict valueForKey:@"doctoruid"];
        if (!doctoruid) {
            doctoruid = @"";
        }
        NSString *doctorname = [dict valueForKey:@"doctorname"];
        if (!doctorname) {
            doctorname = @"";
        }
        NSString *avatar = [dict valueForKey:@"avatar"];
        if (!avatar) {
            avatar = @"";
        }
        NSString *departmentid = [dict valueForKey:@"departmentid"];
        if (!departmentid) {
            departmentid = @"";
        }
        NSString *departmentname = [dict valueForKey:@"departmentname"];
        if (!departmentname) {
            departmentname = @"";
        }
        NSString *doclevelid = [dict valueForKey:@"doclevelid"];
        if (!doclevelid) {
            doclevelid = @"";
        }
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        BOOL success=[_dataBase executeUpdate:@"INSERT INTO SpecifyDoctors (doctoruid,doctorname,avatar,departmentid,departmentname,doclevelid,userID) VALUES (?,?,?,?,?,?,?)",doctoruid,doctorname,avatar,departmentid,departmentname,doclevelid,userID];
        
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (NSArray *)selectAllSpecifyDoctor{
    if ([_dataBase open]) {
        NSMutableArray *allTestResultArray = [[NSMutableArray alloc]init];
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM SpecifyDoctors where userID=%@",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sql];
        while ([resultSet next]) {
            NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"doctoruid"],@"doctoruid",[resultSet stringForColumn:@"doctorname"],@"doctorname",[resultSet stringForColumn:@"avatar"],@"avatar",[resultSet stringForColumn:@"departmentid"],@"departmentid",[resultSet stringForColumn:@"departmentname"],@"departmentname",[resultSet stringForColumn:@"doclevelid"],@"doclevelid", nil];
            [allTestResultArray addObject:dict];
        }
        [_dataBase close];
        return allTestResultArray;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return nil;
}

- (BOOL)deleteSpecifyDoctorWithUID:(NSString *)doctorUID{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        BOOL success=[_dataBase executeUpdate:@"delete from SpecifyDoctors where doctoruid=? and userID=?",doctorUID,userID];
        [_dataBase close];
        return success;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return NO;
}

//安好订阅通知消息红点相关
- (void)insertTheNewestSubscibeNewsMessageWithArray:(NSArray *)newestSubscibeMessageArray{
    if (newestSubscibeMessageArray.count > 0) {
        if ([_dataBase open]) {
            NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
            if (!userID) {
                userID = @"";
            }
            NSDictionary *dict = [newestSubscibeMessageArray lastObject];
            
            NSString *content = [dict valueForKey:@"content"];
            if (!content) {
                content = @"";
            }
            NSString *dateline = @"";
            if ([dict valueForKey:@"dateline"]) {
                dateline = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"dateline"] integerValue]];
            }
            NSString *description = [dict valueForKey:@"description"];
            if (!description) {
                description = @"";
            }
            NSString *nid = @"";
            if ([dict valueForKey:@"nid"]) {
                nid = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"nid"] integerValue]];
            }
            NSString *pic = [dict valueForKey:@"pic"];
            if (!pic) {
                pic = @"";
            }
            NSString *source = [dict valueForKey:@"source"];
            if (!source) {
                source = @"";
            }
            NSString *state = @"";
            if ([dict valueForKey:@"state"]) {
                state = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"state"] integerValue]];
            }
            NSString *title = [dict valueForKey:@"title"];
            if (!title) {
                title = @"";
            }
            
            NSString *sqlString = [NSString stringWithFormat:@"SELECT COUNT(*) FROM NewestSubscibeNewsMessageForUser where userID=%@",userID];
            NSInteger count = [_dataBase intForQuery:sqlString];
            if (count > 0) {//最新数据已存在(updata操作)
                NSString *sql = [NSString stringWithFormat:@"update NewestSubscibeNewsMessageForUser set content='%@',dateline='%@',description='%@',nid='%@',pic='%@',source='%@',state='%@',title='%@',userID='%@',isReaded='%@' where userID = %@",content,dateline,description,nid,pic,source,state,title,userID,@"0",userID];
                BOOL success = [_dataBase executeUpdate:sql];
            }
            else{//最新数据不存在(insert操作)
                BOOL success = [_dataBase executeUpdate:@"INSERT INTO NewestSubscibeNewsMessageForUser (content,dateline,description,nid,pic,source,state,title,userID,isReaded) VALUES (?,?,?,?,?,?,?,?,?,?)",content,dateline,description,nid,pic,source,state,title,userID,@"0"];
            }
            
            [_dataBase close];
        }
        else{
            MyNSLog("打开数据库失败!");
        }
    }
}

- (void)updataSecretaryNewsMessageToReaded{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"update NewestSubscibeNewsMessageForUser set isReaded = 1 where userID = %@", userID];
        [_dataBase executeUpdate:sql];
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (BOOL)isHaveNoReadedSubscibeNewsMessage{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sqlString = [NSString stringWithFormat:@"SELECT COUNT(*) FROM NewestSubscibeNewsMessageForUser where userID=%@ and isReaded = 0",userID];
        NSInteger count = [_dataBase intForQuery:sqlString];
        [_dataBase close];
        if (count > 0) {
            return YES;
        }
        else{
            return NO;
        }
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return NO;
}

- (SubscibeModel *)getTheNewestSecretaryNewsMessage{
    if ([_dataBase open]) {
        SubscibeModel *model;
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM NewestSubscibeNewsMessageForUser where userID=%@",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sql];
        while ([resultSet next]) {
            NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"content"],@"content",[resultSet stringForColumn:@"dateline"],@"dateline",[resultSet stringForColumn:@"description"],@"description",[resultSet stringForColumn:@"nid"],@"nid",[resultSet stringForColumn:@"pic"],@"pic",[resultSet stringForColumn:@"source"],@"source",[resultSet stringForColumn:@"state"],@"state",[resultSet stringForColumn:@"title"],@"title", nil];
            model = [[SubscibeModel alloc]initWithDict:dict];
        }
        [_dataBase close];
        return model;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return nil;
}


//安好订阅内数据存储
- (void)insertReadedSubscibeWithNid:(NSString *)nid{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        [_dataBase executeUpdate:@"INSERT INTO SubscibeReadedTable (nid,userID) VALUES (?,?)",nid,userID];
        
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (BOOL)getIsReadedSubscibeWithNid:(NSString *)nid{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM SubscibeReadedTable where nid=%@ and userID=%@",nid,userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sqlString];
        while ([resultSet next]) {
            NSString *nid = [resultSet stringForColumn:@"nid"];
            if (nid) {
                [_dataBase close];
                return YES;
            }
        }
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return NO;
}


//安好秘书相关
- (void)initSecretarySystemMessageForFirstResignOrLogin{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sqlString = [NSString stringWithFormat:@"SELECT COUNT(*) FROM SecretarySystemMessage where userID=%@ and pmsid = -1",userID];
        NSInteger count = [_dataBase intForQuery:sqlString];
        [_dataBase close];
        if (count > 0) {//这个用户的第一条消息已经存在

        }
        else{
            NSString *picPath = [[NSBundle mainBundle] pathForResource:@"anhaomishu_default" ofType:@"png"];
            NSTimeInterval timerInterval = [[NSDate date]timeIntervalSince1970];
            SecretaryModel *model = [[SecretaryModel alloc]initWithDict:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithDouble:timerInterval],@"dateline",@"感谢您使用安好客户端，即刻享受公立医院认证医生免费咨询服务，查看医院最新号源信息足不出户完成在线挂号，便捷查询周边医院药店，一站式满足您的健康需求。 安好，开启健康新生活。",@"content",picPath,@"pic",@"感谢您使用安好客户端",@"title",@"2",@"type",@"-1",@"pmsid",@"http://m.niruoanhao.com/index/introduction/",@"url" ,nil]];
            [self insertSecretarySystemMessageWithModel:model];
        }
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (void)insertSecretarySystemMessageWithModel:(SecretaryModel *)systemMessageModel{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        BOOL success = [_dataBase executeUpdate:@"INSERT INTO SecretarySystemMessage (content,dateline,pic,pmsid,title,type,url,userID,isReaded) VALUES (?,?,?,?,?,?,?,?,?)",systemMessageModel.content,[NSString stringWithFormat:@"%lf",systemMessageModel.dateline],systemMessageModel.pic,[NSString stringWithFormat:@"%ld",systemMessageModel.pmsid],systemMessageModel.title,[NSString stringWithFormat:@"%ld",systemMessageModel.type],systemMessageModel.url,userID,@"0"];
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (NSArray *)selectAllSecretarySystemMessage{
    if ([_dataBase open]) {
        NSMutableArray *allTestResultArray = [[NSMutableArray alloc]init];
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM SecretarySystemMessage where userID=%@",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sql];
        while ([resultSet next]) {
            NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"content"],@"content",[resultSet stringForColumn:@"dateline"],@"dateline",[resultSet stringForColumn:@"pic"],@"pic",[resultSet stringForColumn:@"pmsid"],@"pmsid",[resultSet stringForColumn:@"title"],@"title",[resultSet stringForColumn:@"type"],@"type",[resultSet stringForColumn:@"url"],@"url",[resultSet stringForColumn:@"userID"],@"userID", nil];
            SecretaryModel *model = [[SecretaryModel alloc]initWithDict:dict];
            if (model.pmsid == -1) {
                [allTestResultArray insertObject:model atIndex:0];
            }
            else{
                [allTestResultArray addObject:model];
            }
        }
        [_dataBase close];
        return allTestResultArray;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return nil;
}

- (BOOL)isHaveNoReadedSystemMessage{    
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM SecretarySystemMessage where userID=%@ and isReaded=0",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sql];
        while ([resultSet next]) {
            NSString *title = [resultSet stringForColumn:@"title"];
            if (title) {
                [_dataBase close];
                return YES;
            }
        }
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return NO;
}

- (void)updataSecretarySystemMessageToReaded{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"update SecretarySystemMessage set isReaded = 1 where userID = %@", userID];
        [_dataBase executeUpdate:sql];
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (SecretaryModel *)getTheNewestSecretarySystemMessage{
    NSArray *allTestResultArray = [self selectAllSecretarySystemMessage];
    if (allTestResultArray && allTestResultArray.count > 0) {
        return [allTestResultArray lastObject];
    }
    return nil;
}


//notice消息相关（圈子）
- (void)insertSecretaryNoticeMessageWithArray:(NSArray *)noticeMessageArray{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        for (NSInteger i=0; i<noticeMessageArray.count; i++) {
            NSDictionary *dict = [noticeMessageArray objectAtIndex:i];
            
            NSString *avatar = @"";
            if ([dict valueForKey:@"avatar"]) {
                avatar = [dict valueForKey:@"avatar"];
            }
            
            NSString *dateline = @"";
            if ([dict valueForKey:@"dateline"]) {
                dateline = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"dateline"] integerValue]];
            }
            NSString *message = @"";
            if ([dict valueForKey:@"message"]) {
                message = [dict valueForKey:@"message"];
            }
            NSString *pcid = @"";
            if ([dict valueForKey:@"pcid"]) {
                pcid = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"pcid"] integerValue]];
            }
            NSString *pmid = @"";
            if ([dict valueForKey:@"pmid"]) {
                pmid = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"pmid"] integerValue]];
            }
            NSString *recvuid = @"";
            if ([dict valueForKey:@"recvuid"]) {
                recvuid = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"recvuid"] integerValue]];
            }
            NSString *recvusername = [dict valueForKey:@"recvusername"];
            if (!recvusername) {
                recvusername = @"";
            }
            NSString *subject = [dict valueForKey:@"subject"];
            if (!subject) {
                subject = @"";
            }
            NSString *summary = [dict valueForKey:@"summary"];
            if (!summary) {
                summary = @"";
            }
            NSString *tid = @"";
            if ([dict valueForKey:@"tid"]) {
                tid = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"tid"] integerValue]];
            }
            NSString *type = @"";
            if ([dict valueForKey:@"type"]) {
                type = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"type"] integerValue]];
            }
            NSString *uid = @"";
            if ([dict valueForKey:@"uid"]) {
                uid = [NSString stringWithFormat:@"%ld",[[dict valueForKey:@"uid"] integerValue]];
            }
            NSString *url = [dict valueForKey:@"url"];
            if (!url) {
                url = @"";
            }
            
            NSString *username = [dict valueForKey:@"username"];
            if (!username) {
                username = @"";
            }
            
            NSString *pid = [dict valueForKey:@"pid"];
            if (!pid) {
                pid = @"";
            }
            
            BOOL success = [_dataBase executeUpdate:@"INSERT INTO SecretaryNoticeMessage (avatar,dateline,message,pcid,pmid,recvuid,recvusername,subject,summary,tid,type,uid,url,username,pid,userID,isReaded) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",avatar,dateline,message,pcid,pmid,recvuid,recvusername,subject,summary,tid,type,uid,url,username,pid,userID,@"0"];
            
            NSArray *picArray = [dict valueForKey:@"pic"];
            for (NSInteger j=0; j<picArray.count; j++) {
                [_dataBase executeUpdate:@"INSERT INTO SecretaryNoticeMessagePic (pmid,userID,picURL) VALUES (?,?,?)",pmid,userID,[picArray objectAtIndex:j]];
            }
        }
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (NSArray *)selectSecretaryNoticeMessage{
    if ([_dataBase open]) {
        NSMutableArray *allResultArray = [[NSMutableArray alloc]init];
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM SecretaryNoticeMessage where userID=%@",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sql];
        while ([resultSet next]) {
            NSMutableArray *allPicArray = [[NSMutableArray alloc]init];
            NSString *pmid = [resultSet stringForColumn:@"pmid"];
            NSString *sqlPic = [NSString stringWithFormat:@"SELECT * FROM SecretaryNoticeMessagePic where pmid=%@",pmid];
            FMResultSet *resultSetForPic=[_dataBase executeQuery:sqlPic];
            while ([resultSetForPic next]) {
                [allPicArray addObject:[resultSetForPic stringForColumn:@"picURL"]];
            }
            NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"avatar"],@"avatar",[resultSet stringForColumn:@"dateline"],@"dateline",[resultSet stringForColumn:@"message"],@"message",allPicArray,@"pic",[resultSet stringForColumn:@"pcid"],@"pcid",[resultSet stringForColumn:@"pmid"],@"pmid",[resultSet stringForColumn:@"recvuid"],@"recvuid",[resultSet stringForColumn:@"recvusername"],@"recvusername",[resultSet stringForColumn:@"subject"],@"subject",[resultSet stringForColumn:@"summary"],@"summary",[resultSet stringForColumn:@"tid"],@"tid",[resultSet stringForColumn:@"type"],@"type",[resultSet stringForColumn:@"uid"],@"uid",[resultSet stringForColumn:@"url"],@"url",[resultSet stringForColumn:@"username"],@"username",[resultSet stringForColumn:@"pid"],@"pid",[resultSet stringForColumn:@"isReaded"],@"isReaded", nil];
            [allResultArray insertObject:[NoticeData modelObjectWithDictionary:dict] atIndex:0];
        }
        [_dataBase close];
        return allResultArray;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return nil;
}

- (NSArray *)selectAllNoReadedSecretaryNoticeMessage{
    if ([_dataBase open]) {
        NSMutableArray *allResultArray = [[NSMutableArray alloc]init];
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM SecretaryNoticeMessage where userID=%@ and isReaded=0",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sql];
        while ([resultSet next]) {
            NSMutableArray *allPicArray = [[NSMutableArray alloc]init];
            NSString *pmid = [resultSet stringForColumn:@"pmid"];
            NSString *sqlPic = [NSString stringWithFormat:@"SELECT * FROM SecretaryNoticeMessagePic where pmid=%@",pmid];
            FMResultSet *resultSetForPic=[_dataBase executeQuery:sqlPic];
            while ([resultSetForPic next]) {
                [allPicArray addObject:[resultSetForPic stringForColumn:@"picURL"]];
            }
            NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"avatar"],@"avatar",[resultSet stringForColumn:@"dateline"],@"dateline",[resultSet stringForColumn:@"message"],@"message",allPicArray,@"pic",[resultSet stringForColumn:@"pcid"],@"pcid",[resultSet stringForColumn:@"pmid"],@"pmid",[resultSet stringForColumn:@"recvuid"],@"recvuid",[resultSet stringForColumn:@"recvusername"],@"recvusername",[resultSet stringForColumn:@"subject"],@"subject",[resultSet stringForColumn:@"summary"],@"summary",[resultSet stringForColumn:@"tid"],@"tid",[resultSet stringForColumn:@"type"],@"type",[resultSet stringForColumn:@"uid"],@"uid",[resultSet stringForColumn:@"url"],@"url",[resultSet stringForColumn:@"username"],@"username",[resultSet stringForColumn:@"pid"],@"oid",[resultSet stringForColumn:@"isReaded"],@"isReaded", nil];
            [allResultArray insertObject:[NoticeData modelObjectWithDictionary:dict] atIndex:0];
        }
        [_dataBase close];
        return allResultArray;
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return nil;
//    if ([_dataBase open]) {
//        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
//        NSString *sql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM SecretaryNoticeMessage where userID=%@ and isReaded=0",userID];
//        NSInteger count = [_dataBase intForQuery:sql];
//        [_dataBase close];
//        return count;
//    }
//    else{
//        MyNSLog("打开数据库失败!");
//    }
//    return 0;
}

- (void)updataSecretaryNoticeMessageToReaded{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"update SecretaryNoticeMessage set isReaded = 1 where userID = %@ and pmid in (select pmid from SecretaryNoticeMessage where isReaded=0)", userID];
        [_dataBase executeUpdate:sql];
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (NoticeData *)getTheNewestSecretaryNoticeMessage{
    NSArray *allResultArray = [self selectSecretaryNoticeMessage];
    if (allResultArray && allResultArray.count > 0) {
        return [allResultArray lastObject];
    }
    return nil;
}

#pragma 上传记步数据相关
//[self createTableWithSQL:@"CREATE TABLE StepCountUploadForDay (dayString text,dayAllSteps text,userID text)"];//添加心率测试表
- (void)insertStepCountUploadForDay:(NSString *)dayString andDayAllSteps:(NSUInteger)allSteps{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        
        NSString *allStepsString = [NSString stringWithFormat:@"%ld",allSteps];
        
        BOOL success = [_dataBase executeUpdate:@"INSERT INTO StepCountUploadForDay (dayString,dayAllSteps,userID) VALUES (?,?,?)",dayString,allStepsString,userID];
            
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

//[self createTableWithSQL:@"CREATE TABLE StepCountUploadForDay (dayString text,dayAllSteps text,userID text)"];//添加心率测试表
- (void)updataStepCountUploadForDay:(NSString *)dayString andDayAllSteps:(NSUInteger)allSteps{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sql = [NSString stringWithFormat:@"update StepCountUploadForDay set dayString='%@',dayAllSteps='%@' where userID = %@",dayString,[NSString stringWithFormat:@"%ld",allSteps],userID];
        [_dataBase executeUpdate:sql];
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
}

- (NSString *)selectToGetUserDefaultStoredDay{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM StepCountUploadForDay where userID=%@",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sqlString];
        while ([resultSet next]) {
            NSString *dayString = [resultSet stringForColumn:@"dayString"];

            [_dataBase close];

            return dayString;
        }
        
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return @"";
}

- (NSUInteger)selectToGetUserDefaultStoredAllStepsForDay{
    if ([_dataBase open]) {
        NSString *userID = [[UserManager shareInstance] getUserInfo].uid;
        if (!userID) {
            userID = @"";
        }
        NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM StepCountUploadForDay where userID=%@",userID];
        FMResultSet *resultSet=[_dataBase executeQuery:sqlString];
        while ([resultSet next]) {
            NSString *dayString = [resultSet stringForColumn:@"dayAllSteps"];
            return [dayString integerValue];
        }
        
        [_dataBase close];
    }
    else{
        MyNSLog("打开数据库失败!");
    }
    return 0;
}

@end
